# -*- coding: utf-8 -*-
# @Time     : 2021/10/14 22:24
# @Author   : JustFly
# @File     : func.py
# @Software : PyCharm

import requests
import json
import time
import pymysql
import traceback
from selenium import webdriver

def get_conn():
    """
    :return: 连接, 游标
    """
    # 创建连接
    conn = pymysql.connect(host="127.0.0.1",
                           user="root",
                           password="123456",
                           db="covid_2019",
                           )
    # 创建游标
    cursor = conn.cursor()
    return conn, cursor

def close_conn(conn, cursor):
    if cursor:
        cursor.close()
    if conn:
        conn.close()

def query(sql, *args):
    """
    封装通用查询
    :param sql:
    :param args:
    :return:元组
    """
    conn, cursor = get_conn()
    cursor.execute(sql, args)
    res = cursor.fetchall()
    # print(res)
    close_conn(conn, cursor)
    return res

def get_top2_time():
    time_str = time.strftime(f"%Y{{}}%m{{}}%d{{}} %X")  # 不支持识别中文 {}站位
    return time_str.format("年", "月", "日")

def get_mid1_data():
    """
    :return: 返回全国总计数据供 div id = "mid-1" 使用
    """
    # sql = "select sum(confirm)," \
    #       "(select suspect from history order by ds desc limit 1)," \
    #       "sum(heal)," \
    #       "sum(dead)" \
    #       "from details;"

    sql = "select confirm, suspect, heal, dead from history order by ds desc limit 1;"

    res = query(sql)
    # print(res)
    return res[0]

def get_mid2_data():
    """
    :return: 返回个省数据供 div id = "mid-2" 使用
    """
    sql = "select province, sum(confirm) from details " \
          "where update_time=(select update_time from details order by update_time desc limit 1)" \
          "group by province"


    res = query(sql)
    return res

def get_left1_data():
    """
    :return:全国累计趋势数据
    """
    sql = "select ds, confirm, suspect, heal, dead from history"
    res = query(sql)
    return res

def get_left2_data():
    """
    :return:全国新增趋势数据
    """
    sql = "select ds, confirm_add, suspect_add, heal_add, dead_add from history"
    res = query(sql)
    return res

def get_right1_data():
    """
    :return: 确诊前五非湖北城市
    """
    # 除去直辖市，因为它里面直接是某某区而非市
    sql = "select city, confirm from" \
          "(" \
          "select city, confirm from details where update_time=(select update_time from details order by update_time desc limit 1)" \
          "and province not in ('台湾', '湖北', '北京', '上海', '天津', '重庆')" \
          "union all " \
          "select province as city, sum(confirm) as confirm from details " \
          "where update_time=(select update_time from details order by update_time desc limit 1)" \
          "and province in ('北京', '上海', '天津', '重庆') group by province" \
          ") as a " \
          "where city not in ('境外输入', '地区待确认') " \
          "order by confirm desc limit 5"

    res = query(sql)

    return res

def get_right2_data():
    """
    :return: 返回最近20条热搜
    """
    sql = "select title, hotscore from bdhot order by id desc limit 20"
    res = query(sql)

    return res




get_right2_data()




